﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using PLC;
using 际海环境软件.Class.sys;

namespace 际海环境软件.Class
{
    class MySqlHelp
    {
        public static string MySqlCon = 际海环境软件.Properties.Settings.Default.MySqlCon;
        public static DataTable GetRefTable()
        {
            try
            {
                string sql;
                sql = "select an_ref as '氨气参考%'," +
                                                           "temp_ref as '温度参考℃',  " +
                                                           "humi_ref as '湿度参考%'," +
                                                           "h2s_ref as '硫化氢参考%'," +
                                                           "voc_ref as '风速参考ug/m3'," +
                                                           "pm25_ref as 'PM2.5参考'" +                                                         
                                                           "from enviroument_ref where id =1;";
                return MySqlBase.GetDataTabel(sql);

            }
            catch (MySqlException ex)
            {
                //Form1.Logger.Error("读取参考值错误 "+ex.ToString());
                return new DataTable();
            }
        }

        public static DataTable GetHistoryData(DateTime StartTime, DateTime EndTime,int ID)
        {
            try
            {
                string sql;
                sql = "select an_val as '氨气含量(%)'," +                                                         
                                                            "temp_val as '温度(℃)'," +
                                                            " humi_val as '湿度(%)'," +
                                                            "h2s_val as '硫化氢(%)'," +
                                                            "voc_val as 'VOC(ug/m3)'," +
                                                            "pm25_val as 'PM2.5'," +                                                          
                                                            "case result when 0 then '合格' else  '不合格' end as '合格？', create_time as '创建时间' from enviroument_data where id=@ID AND  create_time BETWEEN @StartTime AND @EndTime order by create_time desc;";
                return MySqlBase.GetDataTabel(sql,
                   new MySqlParameter("@StartTime", StartTime),
                   new MySqlParameter("@EndTime", EndTime),
                   new MySqlParameter("@ID", ID)
                  );

            }
            catch (MySqlException ex)
            {
                //Form1.Logger.Error("读取历史数据错误 " + ex.ToString());
                return new DataTable();
            }
        }

        public static int InsertData(double an, double temp, double humi, double h2s, double voc, double pm25,int id,int result,DateTime creatTime)
        {
            try
            {
                string sql;
                sql = "INSERT INTO enviroument_data(an_val, temp_val,humi_val,h2s_val,voc_val,pm25_val,id,result,create_time) values(@an,@temp,@humi,@h2s,@voc,@pm25,@id,@result,@creatTime)";
                return MySqlBase.ExecuteSQL(sql,
                   new MySqlParameter("@an", an),
                   new MySqlParameter("@temp", temp),
                   new MySqlParameter("@humi", humi),
                   new MySqlParameter("@h2s", h2s),
                   new MySqlParameter("@voc", voc),
                   new MySqlParameter("@pm25", pm25),
                   new MySqlParameter("@id", id),
                   new MySqlParameter("@result", result),
                   new MySqlParameter("@creatTime", creatTime)
                  );

            }
            catch
            {              
                return 0;
            }
        }

        public static double[] GetRefData()
        {
            double[] Refval = new double[12];
            try
            {
                DataTable dataTable= GetRefTable();
                if (dataTable!=null)
                {                 
                    for (int i = 0; i < 6; i++)
                    {
                        string Rdata = dataTable.Rows[0][i].ToString();
                        string[] RdataSplit = Rdata.Split('-');
                        Refval[i * 2] = Convert.ToSingle(RdataSplit[0]);
                        Refval[i * 2 + 1] = Convert.ToSingle(RdataSplit[1]);
                    }
                    return Refval;
                }
                else { return null; }
            }
            catch
            {
                return null;
            }
        }

        public static int GetWarnNum(string name,double val,string daySet,int type)
        {
            int value = 0;
            try
            {
                
                string sql;
                if (type == 1)
                {
                    sql = "select count(*) from enviroument_data where (" + name + " > @val) and DATE_SUB(CURDATE()," + daySet + " ) <= date(create_time);";
                }
                else 
                {
                    sql = "select count(*) from enviroument_data where (" + name + " < @val) and DATE_SUB(CURDATE()," + daySet + " ) <= date(create_time);";
                }
                DataTable dataTable = MySqlBase.GetDataTabel(sql,                   
                    new MySqlParameter("@val", val));
                if (dataTable!=null)
                {                    
                   string Rdata = dataTable.Rows[0][0].ToString();
                   value = Convert.ToInt32(Rdata);
                }
                return value;                             
            }
            catch
            {
                return value;
            }
        }


        public static DataTable GetHistoryOrigi(DateTime StartTime, DateTime EndTime,int ID)
        {
            try
            {
                string sql;
                sql = "SELECT result FROM `funeral`.`enviroument_data` WHERE id=@ID AND create_time BETWEEN @StartTime AND @EndTime order by create_time desc;";
                return MySqlBase.GetDataTabel(sql,
                   new MySqlParameter("@StartTime", StartTime),
                   new MySqlParameter("@EndTime", EndTime),
                   new MySqlParameter("@ID", ID)
                  );

            }
            catch (MySqlException ex)
            {
                //Form1.Logger.Error("读取历史数据（原始）错误 " + ex.ToString());
                return new DataTable();
            }
        }


    }
}
